<?php
	// koneksi ke mysql
	require_once "../../config/koneksi.php";

	// menggunakan class phpExcelReader
	include "../../config/excel_reader2.php";

	// membaca file excel yang diupload
	$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);

	// membaca jumlah baris dari data excel
	$baris = $data->rowcount($sheet_index=0);

	// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
	$sukses = 0;
	$gagal = 0;

	// import data excel mulai baris ke-2 (karena baris pertama adalah nama kolom)
	for ($i=2; $i<=$baris; $i++){
		$kode = $data->val($i, 2);
		$nama = $data->val($i, 3);
		$id_jenis = $data->val($i, 4);
		$jenis = $data->val($i, 5);
		$jml = mysql_num_rows(mysql_query("select id_layanan from master_layanan where id_layanan = '$kode'"));
		if($jml > 0){
			$query = "update master_layanan set layanan = '$nama', id_jenis_layanan = $id_jenis where id_layanan = '$kode'";
		}else{
			$query = "insert into master_layanan values ('$kode','$nama',$id_jenis)";
		}
		
		$hasil = mysql_query($query);
		if ($hasil){
			$sukses++;
		}
		else{
			$gagal++;
			$data_gagal[$gagal] = array(
				"id_layanan" => "$kode",
				"nama" => "$nama",
				"id_jenis" => "$id_jenis",
				"jenis" => "$jenis"
			);
		}
	}
	
	
?>
<?php
	if($gagal > 0){
	  require_once('../../export/Worksheet.php');
	  require_once('../../export/Workbook.php');
	  // koneksi ke mysql
	  require_once('../../config/koneksi.php');

	  // function untuk membuat header file excel
	  function HeaderingExcel() {
		  $tgl = date("dmY");
		  header("Content-type: application/vnd.ms-excel");
		  header("Content-Disposition: attachment; filename=Import_Layanan_Gagal.xls" );
		  header("Expires: 0");
		  header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
		  header("Pragma: public");
		  }

	  // membuat header file excel dan nama filenya
	  HeaderingExcel('Data Import Obat Gagal.xls');

	  // membuat workbook baru
	  $workbook = new Workbook("");
	  // membuat worksheet ke-1 (data laki-laki)
	  $worksheet1 =& $workbook->add_worksheet('Data Obat');

	  // setting format header tabel data
	  $format =& $workbook->add_format();
	  $format->set_align('vcenter');
	  $format->set_align('center');
	  $format->set_color('white');
	  $format->set_bold();
	  $format->set_italic();
	  $format->set_pattern();
	  $format->set_fg_color('green');
	  
	  // membuat header tabel dengan format
	  $worksheet1->set_row(0, 15);
	  $worksheet1->set_column(0, 1, 5);
	  $worksheet1->write_string(0, 1, "Sukses");
	  $worksheet1->set_column(0, 2, 10);
	  $worksheet1->write_number(0, 2, "$sukses");
	  
	  $worksheet1->set_row(1, 15);
	  $worksheet1->set_column(1, 1, 5);
	  $worksheet1->write_string(1, 1, "Gagal");
	  $worksheet1->set_column(1, 2, 10);
	  $worksheet1->write_number(1, 2, "$gagal");
	  
	  // membuat header tabel dengan format
	  $worksheet1->set_row(3, 15);
	  $worksheet1->set_column(3, 0, 5);
	  $worksheet1->write_string(3, 0, "NO", $format);
	  $worksheet1->set_column(3, 1, 10);
	  $worksheet1->write_string(3, 1, "KODE", $format);
	  $worksheet1->set_column(3, 2, 50);
	  $worksheet1->write_string(3, 2, "NAMA LAYANAN", $format);
	  $worksheet1->set_column(3, 3, 15);
	  $worksheet1->write_string(3, 3, "KODE JENIS", $format);
	  $worksheet1->set_column(3, 4, 30);
	  $worksheet1->write_string(3, 4, "JENIS LAYANAN", $format);

	  // menampilkan data mhasiswa laki-laki
	  $baris = 4;
	  for($i=1;$i<=$gagal;$i++){
			$no++;
			$worksheet1->write_string($baris, 0, $no);
			$worksheet1->write_string($baris, 1, $data_gagal[$no]['id_layanan']);
			$worksheet1->write_string($baris, 2, $data_gagal[$no]['nama']);
			$worksheet1->write_number($baris, 3, $data_gagal[$no]['id_jenis']);
			$worksheet1->write_string($baris, 4, $data_gagal[$no]['jenis']);
			$baris++;
	  }

	  // membuat worksheet ke-2 untuk data mhs perempuan
	  $worksheet2 =& $workbook->add_worksheet('Data Jenis Layanan');

	  // membuat header tabel
	  $worksheet2->set_row(0, 15);
	  $worksheet2->set_column(0, 0, 5);
	  $worksheet2->write_string(0, 0, "KODE", $format);
	  $worksheet2->set_column(0, 1, 30);
	  $worksheet2->write_string(0, 1, "JENIS LAYANAN", $format);

	  // menampilkan data mhasiswa perempuan

	  $query = "SELECT * FROM jenis_layanan";
	  $hasil = mysql_query($query);
	  $baris = 1;
	  while ($data = mysql_fetch_array($hasil))
	  {
			$worksheet2->write_string($baris, 0, $data['id_jenis_layanan']);
			$worksheet2->write_string($baris, 1, $data['jenis_layanan']);
			$baris++;
	  }

	  $workbook->close();
	}else{
		echo "<h3>Proses import data selesai.</h3>";
		echo "<p>Jumlah data yang sukses diimport : ".$sukses."<br>";
		echo "Jumlah data yang gagal diimport : ".$gagal."</p>";
	}
?>